<template>
    <div>
        <input type="file" @change="handleFileUpload" />
    </div>
</template>

<script setup>
import * as XLSX from 'xlsx';
import { ref } from 'vue';
import { Achievement_Of_Course_Objectives } from '@/api/paper'
const handleFileUpload = async (event) => {
    const file = event.target.files[0];
    const reader = new FileReader();
    reader.onload = (e) => {
        const workbook = XLSX.read(e.target.result, { type: 'binary' });
        const sheetName = workbook.SheetNames[0];
        const sheet = workbook.Sheets[sheetName];
        const range = XLSX.utils.decode_range(sheet['!ref']);
        computeStatistics_FOR_OBJ_SCORES(sheet, range)
        const statistics = computeStatistics_FOR_INSERT(sheet, range);
        insertStatisticsIntoSheet(sheet, range, statistics);
        exportModifiedExcel(workbook, 'updated_file.xlsx');
    };
    reader.readAsBinaryString(file);
};
import { useRoute } from 'vue-router';
const route = useRoute();
const subId = parseInt(route.params.subId);
const computeStatistics_FOR_OBJ_SCORES = async (sheet, range) => {
    const TYPE_ROW = 1; // 课程目标所在的行（基于0索引，对应于Excel的第二行）
    const SCORE_START_ROW = 3; // 分数开始的行（基于0索引，对应于Excel的第四行）
    let courseObjectivesTotal = {}; // 用于累加每个课程目标的总分
    let studentCount = 0; // 学生人数
    let typeStatistics = {};
    let currentType = null;
    // 计算学生人数
    studentCount = range.e.r - SCORE_START_ROW + 1;
    for (let C = range.s.c; C <= range.e.c; ++C) {
        const typeCellRef = XLSX.utils.encode_cell({ c: C, r: TYPE_ROW });
        const typeCell = sheet[typeCellRef];
        if (typeCell && typeCell.v) {
            // 提取课程目标编号，如“课程目标1”将提取为“1”
            const objectiveMatch = typeCell.v.match(/课程目标(\d+)/);
            if (objectiveMatch) {
                currentType = `课程目标${objectiveMatch[1]}`; // 重构课程目标名称，保证统一性
                courseObjectivesTotal[currentType] = courseObjectivesTotal[currentType] || 0;
            } else {
                currentType = null; // 非课程目标列
            }
        }

        if (currentType) {
            for (let R = SCORE_START_ROW; R <= range.e.r; ++R) {
                const scoreCellRef = XLSX.utils.encode_cell({ c: C, r: R });
                const scoreCell = sheet[scoreCellRef];
                if (scoreCell && !isNaN(scoreCell.v)) {
                    // 累加分数到对应课程目标的总分
                    courseObjectivesTotal[currentType] += scoreCell.v;
                }
            }
        }
    }

    // 打印每个课程目标的总分
    console.log("studentCount", studentCount);
    console.log("每个课程目标的总分：");
    console.log("Object.entries(courseObjectivesTotal)", Object.entries(courseObjectivesTotal));
    // Object.entries(courseObjectivesTotal).forEach
    let DataToSubmit = []
    for (const [key, value] of Object.entries(courseObjectivesTotal)) {
        const average = value / studentCount;
        DataToSubmit.push({
            type: key,
            score: average.toFixed(2)
        });
        console.log(`${key}: ${average.toFixed(2)}`); // 保留两位小数
    }
    // DataToSubmit.stuNum=studentCount
    // console.log("DataToSubmit", DataToSubmit);
    console.log('data', {
        DataToSubmit,
        studentCount
    });
    await Achievement_Of_Course_Objectives(subId, {
        DataToSubmit,
        studentCount
    })
    return typeStatistics;
};
const QUESTION_ROW = 2;

const computeStatistics_FOR_INSERT = (sheet, range) => {
    const TYPE_ROW = 0;
    const SCORE_START_ROW = 3;
    let typeStatistics = {};
    let currentType = null;

    for (let C = range.s.c; C <= range.e.c; ++C) {
        const typeCellRef = XLSX.utils.encode_cell({ c: C, r: TYPE_ROW });
        const typeCell = sheet[typeCellRef];
        if (typeCell && typeCell.v) {
            currentType = typeCell.v;
            typeStatistics[currentType] = typeStatistics[currentType] || [];
        }

        if (currentType) {
            const questionCellRef = XLSX.utils.encode_cell({ c: C, r: QUESTION_ROW });
            const questionCell = sheet[questionCellRef];
            if (questionCell && typeof questionCell.v === 'number') {
                const scores = [];
                for (let R = SCORE_START_ROW; R <= range.e.r; ++R) {
                    const scoreCellRef = XLSX.utils.encode_cell({ c: C, r: R });
                    const scoreCell = sheet[scoreCellRef];
                    if (scoreCell && !isNaN(scoreCell.v)) {
                        scores.push(scoreCell.v);
                    }
                }
                const sum = scores.reduce((a, b) => a + b, 0);
                const avg = sum / scores.length;
                const max = Math.max(...scores);
                const min = Math.min(...scores);
                const variance = scores.reduce((acc, score) => acc + Math.pow(score - avg, 2), 0) / scores.length;

                typeStatistics[currentType].push({
                    questionNum: questionCell.v,
                    avg: avg,
                    max: max,
                    min: min,
                    variance: variance,
                    scores: scores
                });
            }
        }
    }

    for (const typeName in typeStatistics) {
        typeStatistics[typeName].sort((a, b) => a.questionNum - b.questionNum);
    }

    return typeStatistics;
};


const insertStatisticsIntoSheet = (sheet, range, statistics) => {
    const insertAtRow = range.e.r + 1; // 统计数据插入的起始行位置
    let insertAtCol = range.s.c + 3; // 统计数据插入的起始列位置

    // 在统计数据的最前面插入一次名称
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow })] = { t: 's', v: '平均值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 1 })] = { t: 's', v: '最大值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 2 })] = { t: 's', v: '最低值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 3 })] = { t: 's', v: '方差' };
    insertAtCol++; // 更新插入列位置以反映新添加的名称列

    for (const typeName in statistics) {
        const statsArray = statistics[typeName];
        // 对于每个类型，插入统计数据
        for (let i = 0; i < statsArray.length; i++) {
            const { avg, max, min, variance } = statsArray[i];

            // 插入平均分、最高分、最低分、方差
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow })] = { t: 'n', v: avg, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 1 })] = { t: 'n', v: max, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 2 })] = { t: 'n', v: min, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 3 })] = { t: 'n', v: variance, z: '0.00' };

            insertAtCol++; // 更新插入列位置
        }
    }
    // 更新整个工作表的范围
    range.e.r = insertAtRow + 3; // 插入了4行数据，所以+3
    sheet['!ref'] = XLSX.utils.encode_range(range);
};
const exportModifiedExcel = (workbook, fileName) => {
    XLSX.writeFile(workbook, fileName);
};
</script>

<style scoped>
table {
    width: 100%;
    border-collapse: collapse;
}

th,
td {
    border: 1px solid #ddd;
    padding: 8px;
    text-align: left;
}

h3 {
    margin-top: 20px;
}
</style>